<?php
class Candidate_model extends CI_Model {

    function __construct()
    {
        parent::__construct();
    }
    
    function get($data)
    {
    	if (isset($data['idmember'])) {
    		$this->db->where('idmember', $data['idmember']);
    	}
    	
    	return $this->db->get('candidate')->row();
    }
    
	function get_profile_data($data)
    {
    	$this->db->select('candidate.*, candidate_thumbnail.thumbnail, candidate_thumbnail.image, membership.key');
    	$this->db->join('membership', 'candidate.idmember = membership.id');
    	$this->db->join('candidate_thumbnail', 'candidate_thumbnail.idmember = membership.id AND candidate_thumbnail.idnow = \'Y\'', 'left');
    	$this->db->from('candidate');
    	
    	if (isset($data['idmember'])) {
    		$this->db->where('candidate.idmember', $data['idmember']);
    	}
    	
    	return $this->db->get()->row();
    }
    
	function insert($data)
    {
		$data_insert = array(
		   'firstname' => $data['firstname'],
		   'lastname' => $data['lastname'],
		   'idmember' => $data['idmember']
		);
		
		return $this->db->insert('candidate', $data_insert);
    }
    
	function update($data)
    {
		$data_insert = array(
		   'firstname' => $data['firstname'],
		   'lastname' => $data['lastname'],
		   'idcard' => $data['idcard'],
		   'idtravel' => $data['idtravel'],
		   'idflit' => $data['idflit'],
		   'idhomeoffice' => $data['idhomeoffice'],
		   'iddeficiency' => $data['iddeficiency'],
		);
		
		if (isset($data['birthday']) && is_numeric($data['birthday']))
		{
			$data_insert['birthday'] = $data['birthday'];
		}
		
    	if (isset($data['birthmonth']) && is_numeric($data['birthmonth']))
		{
			$data_insert['birthmonth'] = $data['birthmonth'];
		}
		
   		if (isset($data['birthyear']) && is_numeric($data['birthyear']))
		{
			$data_insert['birthyear'] = $data['birthyear'];
		}
		
    	if (isset($data['idgenre']) && is_numeric($data['idgenre']))
		{
			$data_insert['idgenre'] = $data['idgenre'];
		}
		
   	 	if (isset($data['idmaritalstatus']) && is_numeric($data['idmaritalstatus']))
		{
			$data_insert['idmaritalstatus'] = $data['idmaritalstatus'];
		}
		
    	if (isset($data['idcountry']) && is_numeric($data['idcountry']))
		{
			$data_insert['idcountry'] = $data['idcountry'];
		}
		
    	if (isset($data['iddrivelicense']) && is_numeric($data['iddrivelicense']))
		{
			$data_insert['iddrivelicense'] = $data['iddrivelicense'];
		}
		
		$this->db->where('id', $data['idcandidate']);
		$this->db->where('idmember', $data['idmember']);
		return $this->db->update('candidate', $data_insert);
    }
    
    function get_profile_jobs($data = null)
    {
    	return $this->db->query('select
						job.*, company.name as company
						from candidate_job_preference
						inner join job on job.idcategory = candidate_job_preference.idcategory
						left join company on job.idcompany = company.id
						where (candidate_job_preference.idcandidate = '.$this->session->userdata['idprofile'] .')
						and not exists (SELECT subscription.idjob FROM subscription
						WHERE subscription.idcandidate = '.$this->session->userdata['idprofile'] .' AND subscription.idjob = job.id)
						and (candidate_job_preference.idcountry = job.idcountry or candidate_job_preference.idcountry is null)
						and (exists (select candidate_job_specialty.idspecialty
								from candidate_job_specialty
								where candidate_job_specialty.idjob = candidate_job_preference.id
								and candidate_job_specialty.idspecialty = job.idspecialty)
						or candidate_job_preference.idspecialty = \'N\'
						)
						and (exists (select candidate_job_hierarchy.idhierarchy
								from candidate_job_hierarchy 
								where candidate_job_hierarchy.idjob = candidate_job_preference.id
								and candidate_job_hierarchy.idhierarchy = job.idhierarchy)
						or candidate_job_preference.idhierarchy = \'N\'
						)
						and (exists (select candidate_job_state.idstate 
								from candidate_job_state 
								where candidate_job_state.idjob = candidate_job_preference.id
								and candidate_job_state.idstate = job.idstate)
						or candidate_job_preference.idstate = \'N\'
						)
						and (candidate_job_preference.minsalary between job.minsalary and job.maxsalary
						or candidate_job_preference.maxsalary between job.minsalary and job.maxsalary
						or (candidate_job_preference.minsalary < job.minsalary and candidate_job_preference.maxsalary > job.maxsalary)
						)');
    }
    
	function get_complete_profile($data)
    {
    	$this->db->select('candidate.*, candidate_thumbnail.image, candidate_thumbnail.thumbnail, membership.key,
    	marital_status.name as marital_status, country.name as country, genre.name as genre,
    	drive_license.name as drive_license');
    	$this->db->join('membership', 'candidate.idmember = membership.id');
    	$this->db->join('marital_status', 'candidate.idmaritalstatus = marital_status.id', 'left');
    	$this->db->join('genre', 'candidate.idgenre = genre.id', 'left');
    	$this->db->join('country', 'candidate.idcountry = country.id', 'left');
    	$this->db->join('drive_license', 'candidate.iddrivelicense = drive_license.id', 'left');
    	$this->db->join('candidate_thumbnail', 'candidate_thumbnail.idmember = membership.id AND candidate_thumbnail.idnow = \'Y\'', 'left');
    	$this->db->from('candidate');
    	
    	if (isset($data['idmember'])) {
    		$this->db->where('candidate.idmember', $data['idmember']);
    	}
    	
    	if (isset($data['idcandidate'])) {
    		$this->db->where('candidate.id', $data['idcandidate']);
    	}
    	
    	return $this->db->get()->row();
    }
    
    function search($data)
    {
    	$this->db->select('candidate.id, (SELECT date FROM membership_login WHERE membership_login.idmember = candidate.idmember LIMIT 1) AS date, candidate.idmember AS idmember');
    	$this->db->from('candidate');
    	$this->db->join('membership', 'candidate.idmember = membership.id');
    	 
    	if (isset($data['keyword'])) {
    		$this->db->like('candidate.firstname', $data['keyword']);
    		$this->db->or_like('candidate.lastname', $data['keyword']);
    	}
    	 
    	$this->db->order_by('candidate.firstname');
    	
    	if (!isset($data['limit']) || !is_numeric($data['limit']))
    	{
    		$data['limit'] = 15;
    	}
    	 
    	if (!isset($data['offset']) || !is_numeric($data['offset']))
    	{
    		$data['offset'] = 0;
    	}
    	elseif ($data['offset'] > 0)
    	{
    		$data['offset'] = ($data['limit'] * $data['offset']);
    	}
    	 
    	$this->db->limit($data['limit'], $data['offset']);
    	
    	return $this->db->get();
    }
    
    function get_count_professionals($data)
    {
    	$this->db->select('candidate.id, (SELECT date FROM membership_login WHERE membership_login.idmember = candidate.idmember LIMIT 1) AS date, candidate.idmember AS idmember');
    	$this->db->from('candidate');
    	$this->db->join('membership', 'candidate.idmember = membership.id');
    	 
    	if (isset($data['keyword'])) {
    		$this->db->like('candidate.firstname', $data['keyword']);
    		$this->db->or_like('candidate.lastname', $data['keyword']);
    	}
    	
    	return $this->db->count_all_results();
    }
}
?>